<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>MySQL DELETE、DROP 和 TRUNCATE 操作详解</title>
    <link href="https://cdn.staticfile.org/font-awesome/6.4.0/css/all.min.css" rel="stylesheet">
    <link href="https://cdn.staticfile.org/tailwindcss/2.2.19/tailwind.min.css" rel="stylesheet">
    <link href="https://fonts.googleapis.com/css2?family=Noto+Serif+SC:wght@400;500;600;700&family=Noto+Sans+SC:wght@300;400;500;700&display=swap" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/mermaid@latest/dist/mermaid.min.js"></script>
    <style>
        body {
            font-family: 'Noto Sans SC', Tahoma, Arial, Roboto, "Droid Sans", "Helvetica Neue", "Droid Sans Fallback", "Heiti SC", "Hiragino Sans GB", Simsun, sans-serif;
            line-height: 1.8;
            color: #333;
        }
        h1, h2, h3, h4 {
            font-family: 'Noto Serif SC', serif;
            font-weight: 600;
        }
        .hero-gradient {
            background: linear-gradient(135deg, #6e8efb 0%, #a777e3 100%);
        }
        .code-block {
            background-color: #f8f9fa;
            border-left: 4px solid #6e8efb;
            color: #333;
        }
        .card-hover:hover {
            transform: translateY(-5px);
            box-shadow: 0 10px 25px rgba(0,0,0,0.1);
        }
        .feature-icon {
            width: 60px;
            height: 60px;
            border-radius: 50%;
            display: flex;
            align-items: center;
            justify-content: center;
            margin: 0 auto 20px;
        }
    </style>
</head>
<body class="bg-gray-50">
    <!-- Hero Section -->
    <section class="hero-gradient text-white py-20 px-4 md:px-0">
        <div class="container mx-auto max-w-6xl text-center">
            <h1 class="text-4xl md:text-5xl font-bold mb-6">MySQL 数据操作三剑客</h1>
            <p class="text-xl md:text-2xl font-light mb-8 max-w-3xl mx-auto">深入了解 DELETE、DROP 和 TRUNCATE 的区别与应用场景</p>
            <div class="flex justify-center space-x-4">
                <a href="#comparison" class="bg-white text-purple-700 px-6 py-3 rounded-lg font-medium hover:bg-opacity-90 transition duration-300">快速对比</a>
                <a href="#details" class="border-2 border-white text-white px-6 py-3 rounded-lg font-medium hover:bg-white hover:bg-opacity-10 transition duration-300">详细解析</a>
            </div>
        </div>
    </section>

    <!-- Comparison Section -->
    <section id="comparison" class="py-16 px-4 md:px-0 bg-white">
        <div class="container mx-auto max-w-6xl">
            <h2 class="text-3xl font-bold text-center mb-12 text-gray-800">操作对比一览</h2>
            
            <div class="grid md:grid-cols-3 gap-8">
                <!-- DELETE Card -->
                <div class="bg-white rounded-xl shadow-md overflow-hidden transition-all duration-300 card-hover">
                    <div class="bg-red-100 p-6">
                        <div class="feature-icon bg-red-500 text-white">
                            <i class="fas fa-trash-alt text-2xl"></i>
                        </div>
                        <h3 class="text-2xl font-bold text-center text-gray-800 mb-2">DELETE</h3>
                        <p class="text-gray-600 text-center">行级数据删除操作</p>
                    </div>
                    <div class="p-6">
                        <ul class="space-y-3">
                            <li class="flex items-start">
                                <i class="fas fa-check-circle text-green-500 mt-1 mr-2"></i>
                                <span>删除指定行数据</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-check-circle text-green-500 mt-1 mr-2"></i>
                                <span>支持 WHERE 条件筛选</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-check-circle text-green-500 mt-1 mr-2"></i>
                                <span>可回滚的事务操作</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-info-circle text-blue-500 mt-1 mr-2"></i>
                                <span>保留表结构和索引</span>
                            </li>
                        </ul>
                    </div>
                </div>

                <!-- TRUNCATE Card -->
                <div class="bg-white rounded-xl shadow-md overflow-hidden transition-all duration-300 card-hover">
                    <div class="bg-blue-100 p-6">
                        <div class="feature-icon bg-blue-500 text-white">
                            <i class="fas fa-broom text-2xl"></i>
                        </div>
                        <h3 class="text-2xl font-bold text-center text-gray-800 mb-2">TRUNCATE</h3>
                        <p class="text-gray-600 text-center">表数据快速清空</p>
                    </div>
                    <div class="p-6">
                        <ul class="space-y-3">
                            <li class="flex items-start">
                                <i class="fas fa-check-circle text-green-500 mt-1 mr-2"></i>
                                <span>快速清空全表数据</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-check-circle text-green-500 mt-1 mr-2"></i>
                                <span>不记录单行删除日志</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-info-circle text-blue-500 mt-1 mr-2"></i>
                                <span>保留表结构</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-exclamation-circle text-yellow-500 mt-1 mr-2"></i>
                                <span>通常不可回滚</span>
                            </li>
                        </ul>
                    </div>
                </div>

                <!-- DROP Card -->
                <div class="bg-white rounded-xl shadow-md overflow-hidden transition-all duration-300 card-hover">
                    <div class="bg-purple-100 p-6">
                        <div class="feature-icon bg-purple-500 text-white">
                            <i class="fas fa-dumpster-fire text-2xl"></i>
                        </div>
                        <h3 class="text-2xl font-bold text-center text-gray-800 mb-2">DROP</h3>
                        <p class="text-gray-600 text-center">表结构完全删除</p>
                    </div>
                    <div class="p-6">
                        <ul class="space-y-3">
                            <li class="flex items-start">
                                <i class="fas fa-check-circle text-green-500 mt-1 mr-2"></i>
                                <span>彻底删除表和数据</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-exclamation-circle text-red-500 mt-1 mr-2"></i>
                                <span>不可恢复的操作</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-exclamation-circle text-red-500 mt-1 mr-2"></i>
                                <span>不支持事务回滚</span>
                            </li>
                            <li class="flex items-start">
                                <i class="fas fa-info-circle text-blue-500 mt-1 mr-2"></i>
                                <span>同时删除索引和约束</span>
                            </li>
                        </ul>
                    </div>
                </div>
            </div>

            <!-- Mermaid Diagram -->
            <div class="mt-16 bg-white rounded-xl shadow-md p-6">
                <h3 class="text-2xl font-bold mb-6 text-gray-800">操作特性对比图</h3>
                <div class="mermaid">
                    flowchart TD
                        A[MySQL 数据操作] --> B(DELETE)
                        A --> C(TRUNCATE)
                        A --> D(DROP)
                        
                        B --> E[逐行删除数据]
                        B --> F[支持WHERE条件]
                        B --> G[可回滚]
                        B --> H[保留表结构]
                        
                        C --> I[快速清空数据]
                        C --> J[不记录单行日志]
                        C --> K[通常不可回滚]
                        C --> L[保留表结构]
                        
                        D --> M[删除表和数据]
                        D --> N[不可恢复]
                        D --> O[不支持事务]
                        D --> P[删除索引约束]
                </div>
            </div>
        </div>
    </section>

    <!-- Details Section -->
    <section id="details" class="py-16 px-4 md:px-0 bg-gray-50">
        <div class="container mx-auto max-w-6xl">
            <h2 class="text-3xl font-bold text-center mb-12 text-gray-800">详细操作解析</h2>
            
            <!-- DELETE Section -->
            <div class="bg-white rounded-xl shadow-md overflow-hidden mb-12">
                <div class="bg-red-500 text-white p-6">
                    <h3 class="text-2xl font-bold flex items-center">
                        <i class="fas fa-trash-alt mr-3"></i> DELETE 操作
                    </h3>
                </div>
                <div class="p-6 md:p-8">
                    <p class="text-lg text-gray-700 mb-6"><strong>DELETE</strong> 用于删除表中的数据记录，但不删除表结构。它是行级的删除操作，可以通过 WHERE 子句精确控制要删除的行。</p>
                    
                    <div class="grid md:grid-cols-2 gap-8">
                        <div>
                            <h4 class="text-xl font-semibold mb-4 text-gray-800 border-b pb-2">核心特性</h4>
                            <ul class="space-y-3">
                                <li class="flex items-start">
                                    <i class="fas fa-check text-red-500 mt-1 mr-2"></i>
                                    <span><strong>逐行删除</strong>：每删除一行都会记录日志，触发删除触发器</span>
                                </li>
                                <li class="flex items-start">
                                    <i class="fas fa-check text-red-500 mt-1 mr-2"></i>
                                    <span><strong>事务支持</strong>：在事务中可回滚，保证数据一致性</span>
                                </li>
                                <li class="flex items-start">
                                    <i class="fas fa-check text-red-500 mt-1 mr-2"></i>
                                    <span><strong>条件筛选</strong>：支持 WHERE 子句指定删除条件</span>
                                </li>
                                <li class="flex items-start">
                                    <i class="fas fa-check text-red-500 mt-1 mr-2"></i>
                                    <span><strong>保留结构</strong>：不影响表结构、索引和约束</span>
                                </li>
                            </ul>
                        </div>
                        <div>
                            <h4 class="text-xl font-semibold mb-4 text-gray-800 border-b pb-2">典型应用场景</h4>
                            <ul class="space-y-3">
                                <li class="flex items-start">
                                    <i class="fas fa-database text-blue-500 mt-1 mr-2"></i>
                                    <span>删除特定的业务数据记录</span>
                                </li>
                                <li class="flex items-start">
                                    <i class="fas fa-database text-blue-500 mt-1 mr-2"></i>
                                    <span>需要事务保证的数据删除操作</span>
                                </li>
                                <li class="flex items-start">
                                    <i class="fas fa-database text-blue-500 mt-1 mr-2"></i>
                                    <span>只删除部分数据而不影响表结构</span>
                                </li>
                            </ul>
                        </div>
                    </div>
                    
                    <div class="mt-8">
                        <h4 class="text-xl font-semibold mb-4 text-gray-800 border-b pb-2">代码示例</h4>
                        <div class="code-block p-4 rounded-lg font-mono text-sm overflow-x-auto">
                            <pre>DELETE FROM employees WHERE department = 'Sales';</pre>
                        </div>
                        <p class="text-gray-600 mt-2">这条语句会删除 <code class="bg-gray-100 px-1 rounded">employees</code> 表中所有部门为 <code class="bg-gray-100 px-1 rounded">Sales</code> 的员工记录。</p>
                    </div>
                </div>
            </div>
            
            <!-- DROP Section -->
            <div class="bg-white rounded-xl shadow-md overflow-hidden mb-12">
                <div class="bg-purple-500 text-white p-6">
                    <h3 class="text-2xl font-bold flex items-center">
                        <i class="fas fa-dumpster-fire mr-3"></i> DROP 操作
                    </h3>
                </div>
                <div class="p-6 md:p-8">
                    <p class="text-lg text-gray-700 mb-6"><strong>DROP</strong> 用于完全删除数据库对象（表、数据库或视图等），包括其所有数据和结构。这是一个不可逆的破坏性操作。</p>
                    
                    <div class="grid md:grid-cols-2 gap-8">
                        <div>
                            <h4 class="text-xl font-semibold mb-4 text-gray-800 border-b pb-2">核心特性</h4>
                            <ul class="space-y-3">
                                <li class="flex items-start">
                                    <i class="fas fa-check text-purple-500 mt-1 mr-2"></i>
                                    <span><strong>彻底删除</strong>：完全删除表及其所有数据</span>
                                </li>
                                <li class="flex items-start">
                                    <i class="fas fa-exclamation text-purple-500 mt-1 mr-2"></i>
                                    <span><strong>不可恢复</strong>：删除后无法找回数据</span>
                                </li>
                                <li class="flex items-start">
                                    <i class="fas fa-exclamation text-purple-500 mt-1 mr-2"></i>
                                    <span><strong>无事务支持</strong>：立即生效，不能回滚</span>
                                </li>
                                <li class="flex items-start">
                                    <i class="fas fa-check text-purple-500 mt-1 mr-2"></i>
                                    <span><strong>删除关联对象</strong>：同时删除索引、约束等</span>
                                </li>
                            </ul>
                        </div>
                        <div>
                            <h4 class="text-xl font-semibold mb-4 text-gray-800 border-b pb-2">典型应用场景</h4>
                            <ul class="space-y-3">
                                <li class="flex items-start">
                                    <i class="fas fa-database text-blue-500 mt-1 mr-2"></i>
                                    <span>不再需要的临时表或测试表</span>
                                </li>
                                <li class="flex items-start">
                                    <i class="fas fa-database text-blue-500 mt-1 mr-2"></i>
                                    <span>数据库重构时需要删除的旧表</span>
                                </li>
                                <li class="flex items-start">
                                    <i class="fas fa-database text-blue-500 mt-1 mr-2"></i>
                                    <span>需要彻底清除所有表数据和结构的场景</span>
                                </li>
                            </ul>
                        </div>
                    </div>
                    
                    <div class="mt-8">
                        <h4 class="text-xl font-semibold mb-4 text-gray-800 border-b pb-2">代码示例</h4>
                        <div class="code-block p-4 rounded-lg font-mono text-sm overflow-x-auto">
                            <pre>DROP TABLE employees;</pre>
                        </div>
                        <p class="text-gray-600 mt-2">这条语句会删除 <code class="bg-gray-100 px-1 rounded">employees</code> 表及其所有数据，表结构也会被彻底删除。</p>
                    </div>
                </div>
            </div>
            
            <!-- TRUNCATE Section -->
            <div class="bg-white rounded-xl shadow-md overflow-hidden">
                <div class="bg-blue-500 text-white p-6">
                    <h3 class="text-2xl font-bold flex items-center">
                        <i class="fas fa-broom mr-3"></i> TRUNCATE 操作
                    </h3>
                </div>
                <div class="p-6 md:p-8">
                    <p class="text-lg text-gray-700 mb-6"><strong>TRUNCATE</strong> 用于快速清空表中的所有数据，但保留表结构。它比 DELETE 更高效，适合需要快速重置表数据的场景。</p>
                    
                    <div class="grid md:grid-cols-2 gap-8">
                        <div>
                            <h4 class="text-xl font-semibold mb-4 text-gray-800 border-b pb-2">核心特性</h4>
                            <ul class="space-y-3">
                                <li class="flex items-start">
                                    <i class="fas fa-check text-blue-500 mt-1 mr-2"></i>
                                    <span><strong>高效清空</strong>：快速释放数据页，不记录单行日志</span>
                                </li>
                                <li class="flex items-start">
                                    <i class="fas fa-exclamation text-blue-500 mt-1 mr-2"></i>
                                    <span><strong>有限事务支持</strong>：通常不可回滚（取决于存储引擎）</span>
                                </li>
                                <li class="flex items-start">
                                    <i class="fas fa-check text-blue-500 mt-1 mr-2"></i>
                                    <span><strong>保留结构</strong>：表定义、列、索引等保持不变</span>
                                </li>
                                <li class="flex items-start">
                                    <i class="fas fa-check text-blue-500 mt-1 mr-2"></i>
                                    <span><strong>重置自增</strong>：自增计数器重置为初始值</span>
                                </li>
                            </ul>
                        </div>
                        <div>
                            <h4 class="text-xl font-semibold mb-4 text-gray-800 border-b pb-2">典型应用场景</h4>
                            <ul class="space-y-3">
                                <li class="flex items-start">
                                    <i class="fas fa-database text-blue-500 mt-1 mr-2"></i>
                                    <span>需要快速清空测试数据</span>
                                </li>
                                <li class="flex items-start">
                                    <i class="fas fa-database text-blue-500 mt-1 mr-2"></i>
                                    <span>定期清理日志表等临时数据</span>
                                </li>
                                <li class="flex items-start">
                                    <i class="fas fa-database text-blue-500 mt-1 mr-2"></i>
                                    <span>重置表数据但保留表结构</span>
                                </li>
                            </ul>
                        </div>
                    </div>
                    
                    <div class="mt-8">
                        <h4 class="text-xl font-semibold mb-4 text-gray-800 border-b pb-2">代码示例</h4>
                        <div class="code-block p-4 rounded-lg font-mono text-sm overflow-x-auto">
                            <pre>TRUNCATE TABLE employees;</pre>
                        </div>
                        <p class="text-gray-600 mt-2">这条语句会清空 <code class="bg-gray-100 px-1 rounded">employees</code> 表中的所有数据，但表结构和定义仍然保留，可以继续使用该表插入新数据。</p>
                    </div>
                </div>
            </div>
        </div>
    </section>

    <!-- Summary Section -->
    <section class="py-16 px-4 md:px-0 bg-white">
        <div class="container mx-auto max-w-6xl">
            <h2 class="text-3xl font-bold text-center mb-12 text-gray-800">关键区别总结</h2>
            
            <div class="overflow-x-auto">
                <table class="min-w-full bg-white rounded-lg overflow-hidden">
                    <thead class="bg-gray-800 text-white">
                        <tr>
                            <th class="py-3 px-4 text-left">特性</th>
                            <th class="py-3 px-4 text-left">DELETE</th>
                            <th class="py-3 px-4 text-left">TRUNCATE</th>
                            <th class="py-3 px-4 text-left">DROP</th>
                        </tr>
                    </thead>
                    <tbody class="divide-y divide-gray-200">
                        <tr>
                            <td class="py-4 px-4 font-medium">数据删除 vs. 表删除</td>
                            <td class="py-4 px-4">只删除数据</td>
                            <td class="py-4 px-4">删除所有数据但保留表</td>
                            <td class="py-4 px-4">删除表及所有数据</td>
                        </tr>
                        <tr class="bg-gray-50">
                            <td class="py-4 px-4 font-medium">性能</td>
                            <td class="py-4 px-4">逐行删除，性能较低</td>
                            <td class="py-4 px-4">高效，快速释放数据页</td>
                            <td class="py-4 px-4">快速但不可恢复</td>
                        </tr>
                        <tr>
                            <td class="py-4 px-4 font-medium">事务支持</td>
                            <td class="py-4 px-4">支持回滚</td>
                            <td class="py-4 px-4">通常不可回滚</td>
                            <td class="py-4 px-4">不支持事务</td>
                        </tr>
                        <tr class="bg-gray-50">
                            <td class="py-4 px-4 font-medium">触发器</td>
                            <td class="py-4 px-4">触发删除触发器</td>
                            <td class="py-4 px-4">不触发触发器</td>
                            <td class="py-4 px-4">不适用</td>
                        </tr>
                        <tr>
                            <td class="py-4 px-4 font-medium">自增计数器</td>
                            <td class="py-4 px-4">不重置</td>
                            <td class="py-4 px-4">重置</td>
                            <td class="py-4 px-4">表不存在</td>
                        </tr>
                    </tbody>
                </table>
            </div>
            
            <div class="mt-12 bg-blue-50 rounded-xl p-8 border border-blue-100">
                <h3 class="text-2xl font-bold mb-4 text-blue-800">操作选择指南</h3>
                <ul class="space-y-3">
                    <li class="flex items-start">
                        <i class="fas fa-check-circle text-blue-500 mt-1 mr-2"></i>
                        <span><strong>需要删除特定行</strong> → 使用 DELETE 配合 WHERE 子句</span>
                    </li>
                    <li class="flex items-start">
                        <i class="fas fa-check-circle text-blue-500 mt-1 mr-2"></i>
                        <span><strong>需要快速清空表数据</strong> → 使用 TRUNCATE</span>
                    </li>
                    <li class="flex items-start">
                        <i class="fas fa-check-circle text-blue-500 mt-1 mr-2"></i>
                        <span><strong>需要彻底删除表</strong> → 使用 DROP</span>
                    </li>
                    <li class="flex items-start">
                        <i class="fas fa-check-circle text-blue-500 mt-1 mr-2"></i>
                        <span><strong>需要事务支持</strong> → 使用 DELETE</span>
                    </li>
                </ul>
            </div>
        </div>
    </section>

    <!-- Footer -->
    <footer class="bg-gray-900 text-white py-8 px-4">
        <div class="container mx-auto max-w-6xl">
            <div class="flex flex-col md:flex-row justify-between items-center">
                <div class="mb-4 md:mb-0">
                    <h3 class="text-xl font-bold">技术小馆</h3>
                    <p class="text-gray-400">专业的技术知识分享平台</p>
                </div>
                <div>
                    <a href="http://www.yuque.com/jtostring" class="text-blue-300 hover:text-white transition duration-300">
                        <i class="fas fa-external-link-alt mr-2"></i> http://www.yuque.com/jtostring
                    </a>
                </div>
            </div>
        </div>
    </footer>

    <script>
        mermaid.initialize({
            startOnLoad: true,
            theme: 'default',
            flowchart: {
                useMaxWidth: true,
                htmlLabels: true,
                curve: 'basis'
            }
        });
    </script>
</body>
</html>